
use strict;
use XML::LibXML;
use LWP::Simple;
use DBI;
use Config::IniFiles;

# This program read config_cdip.xml, get the spectral wave data file, parse it, then insert the data to the database. It will automatically update platform, sensor,sensor_type,obs_type,m_type,m_scalar_type,uom_type tables if no related record found. But it can't do the unit conversion yet.

#config
my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $tname=$cfg->val('rcoos','tname');
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');

my($waveheight,$timeperiod,$direction,$awp,$timedate,@freq,@bandwidth,@energy,@dmean,@a1,@b1,@a2,@b2,@r1,@r2,@pridirection,@factor,$counter,$organization_row_id,$platform_row_id,$platform_handle,$m_lon,$m_lat,$sp);  

#for sqlite database
#my $timevalue="datetime('now')";
#my $dbname="waves.db";
#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
#                    { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}

#for postgres database
#establish database connection
my $dbh = DBI->connect ( "dbi:Pg:dbname=$db_name", $db_user, $db_passwd);
if ( !defined $dbh ) {
       die "Cannot connect to database!\n";
       exit 0;
} 


my $timevalue="current_timestamp";

my $missing_value = '-99999';

##################
#read input files for looping through calls
##################

my $target_dir = '/tmp/response';

my $config_filepath = '/var/myscheduler/config_cdip.xml';
my $config = XML::LibXML->new->parse_file($config_filepath);


foreach my $provider ($config->findnodes('//provider')) {
	my $data_url = $provider->findvalue('dataURL'); 
        my $provider_url=$provider->findvalue('url');
	my $startTime = $provider->findvalue('startTime'); 
 	my $endTime = $provider->findvalue('endTime');        
        my $organization=$provider->findvalue('name');
 	my $days = $provider->findvalue('days');         
	my $cdipId = $provider->findvalue('cdipId');
	$sp = $provider->findvalue('sp');
	$platform_handle = $provider->findvalue('platformHandle');
	my $platform_Id = $provider->findvalue('platformId');
        my ($xml_url,$startcount);
  
	my $waverider_Id = $provider->findvalue('waveriderId');
        #=comment
        my $endTime = `date +%Y%m%d%H%M --date='+5 hours'`;
        chomp($endTime);
        my $startTime = `date +%Y%m%d%H%M --date='-2 hours'`;
        chomp($startTime);
        #=cut
        #$endTime="20111024";
        #$startTime="20111013";

        # if the data is from cdip website
        if($cdipId){
          $xml_url = $data_url."$cdipId"."+sp";

          if($startTime){
                 $xml_url .= "+$startTime"; 
          }
          if($endTime){
                 $xml_url .= "+$endTime"; 
          }           
        }
        else {
           # this is getting data from duck frf website
           $days=1;
           if($days){
             $xml_url=$data_url."+".$days;
           }
           else {
              $xml_url=$data_url."+1";
           }
        }
        
        #generate a random file handle concatenating a random number
        my $xml_filepath = "$target_dir/".$platform_Id."latest.txt";
        print $xml_url."\n";
        print $xml_filepath."\n";
        my $content = getstore($xml_url, $xml_filepath);
        die "Couldn't get $xml_url" unless defined $content; 
      
	my $sql = qq{ SELECT row_id from organization where short_name like '$organization' };
	#print $sql."\n";
	my $sth = $dbh->prepare( $sql );
	$sth->execute();
	($organization_row_id) = $sth->fetchrow_array;
        
	if ($organization_row_id) {} #print "$organization_name_lkp\n"; 
	else {
		print "organization not found - inserting\n";

		$sql = qq{ SELECT max(row_id) from organization  };
		#printsub getSensor{
  
    my ($m_type_id,$short_name)=@_; $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($organization_row_id) = $sth->fetchrow_array;	        
                $organization_row_id=$organization_row_id+1;
		$sql = qq{ INSERT into organization(row_id,row_entry_date,short_name,url) values ($organization_row_id,$timevalue,'$organization','$provider_url'); };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
         
	}

	##platform####################
	$sql = qq{ SELECT row_id,short_name,platform_handle from platform where organization_id = $organization_row_id and short_name = '$platform_Id' };
	#print $sql."\n";
	$sth = $dbh->prepare( $sql );
	$sth->execute();
	($platform_row_id) = $sth->fetchrow_array;

	if ($platform_row_id) {
            ;
        } #print "$platform_name_lkp\n"; }
	else {
		print "platform not found - inserting\n";

		#requery for new row_id
		$sql = qq{ SELECT max(row_id) from platform  };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($platform_row_id) = $sth->fetchrow_array;
                $platform_row_id++;
                my $undefined_platform_type_id=6;
                #$platform_desc=~ s/,//g;
                my $platform_url=$provider_url."/station_page.php?station=$platform_Id";
		$sql = "INSERT into platform(row_id,row_entry_date,organization_id,type_id,short_name,platform_handle) values ($platform_row_id,$timevalue,$organization_row_id,$undefined_platform_type_id,'$platform_Id','$platform_handle')";
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
        }

        #get row_id from multi_obs table, should set the multi_obs table row_id autoincrement
        
        if($counter==0){
                      $sql="select max(row_id) from $tname";
                      $sth = $dbh->prepare($sql);
                      $sth->execute();
                      ($counter) = $sth->fetchrow_array;
                      $startcount=$counter;
                      $counter++;
        }  

        print "counter is $counter\n";
        ##############
        #print "\n\n$xml_filepath\n";
        #system("perl /home/qixiaoy/rcoos/wavespectral/fab2Ralpha.pl $xml_filepath");
        #exit 0;
        open(FILE,"<$xml_filepath");
        my @data=<FILE>;
        close(FILE);
  
       #parse the file

        my($linecount,$i);
        $linecount=-1;
        for($i=0;$i<@data;$i++){
                #print $data[$i];             
             if($data[$i]=~ /File Name/){
                  if($i>0){
                     if($timedate){  
                         if(@freq<100){
                            if($sp==0){
                            insert();
                            }
                            else{insert1();}
                          }
                     }
                     $waveheight="";$timeperiod="";
                     $direction="";$awp="";
                     @freq=();@bandwidth=();
                     @energy=();@dmean=();
                     @a1=();@b1=();@a2=();@b2=();@factor=();@pridirection=();@r1=();@r2=();
                  }
                  $linecount=$i;
                  if($cdipId){

                    $data[$i]=~ /(.*)(\S*\d\d\d\d\d)(\d\d\d\d\d\d\d\d\d\d\d\d)(\s+)(Analyzed)(.*)/;
                    #$data[$i]=~ /(.*)(sp\d\d\d\d\d)(\d\d\d\d\d\d\d\d\d\d\d\d)(\s+)(Analyzed)(.*)/;
                    $timedate=substr($3,0,4)."-".substr($3,4,2)."-".substr($3,6,2)." ".substr($3,8,2).":".substr($3,10,2);
       #             print "$3\n$timedate"."\n\n";
                  }
                  else {
                    #if($waverider_Id>0){
                    
                       ###$data[$i]=~ /(.*)(wr\d\d\d\d\d)(\d\d\d\d\d\d\d\d\d\d\d\d)(\s+)(Analyzed)(.*)/;
                       $data[$i]=~ /(\S*\d\d\d\d\d)(\d\d\d\d\d\d\d\d\d\d\d\d)(\s+)(Analyzed)(.*)/;
                    
                        $timedate=substr($2,0,4)."-".substr($2,4,2)."-".substr($2,6,2)." ".substr($2,8,2).":".substr($2,10,2);
                     #    print $timedate."\n";
                    #}
                    #else {
                       
                     #  $data[$i]=~ /(\S*\d\d\d\d\d)(\d\d\d\d\d\d\d\d\d\d\d\d)(\s+)(Analyzed)(.*)/;
                     #  $data[$i]=~ /(.*)(aw\d\d\d\d\d)(\d\d\d\d\d\d\d\d\d\d\d\d)(\s+)(Analyzed)(.*)/;
                        
                    #$timedate=substr($2,0,4)."-".substr($2,4,2)."-".substr($2,6,2)." ".substr($2,8,2).":".substr($2,10,2);
                    #}

                    
                  }

                  
             }
             if(($i>=0)&&($i==($linecount+6))){
                 $data[$i]=~ /(Hs\(m\):)(.*)(Tp\(s\):)(.*)(Dp\(deg\):)(.*)(Ta\(s\):)(.*)/;
                 $waveheight=$2;
                 $timeperiod=$4;
                 $direction=$6;
                 $awp=$8;
             }
             if(($i>=0)&&($i==($linecount+2))){
                 $data[$i]=~ /(Location:)\s+(\d.*)\s(N)\s+(-??)(\d.*)\s(W)(.*)(Sensor)(.*)/;
                 $m_lat=$2;
                 $m_lon=$5;
                 my @temp=split(/ /,$m_lat);
                 $m_lat=$temp[0]+($temp[1]/60.0);
                 my @temp=split(/ /,$m_lon);
                 $m_lon=$temp[0]+($temp[1]/60.0);
                 if($m_lon>0){$m_lon=0-$m_lon;}
                 $m_lon=sprintf("%.4f",$m_lon);
                 $m_lat=sprintf("%.4f",$m_lat);
                 #print "longitude: **".$m_lon."***\n";
             }
             if(($i>=0)&&($i>($linecount+9))){
                ##2d spectral wave;
                 if($sp==0){
                   $data[$i]=~ /(\d+.\d+)\s+(\d+.\d+)\s+(\d+.\d+)\s+(\d+)\s+(-*\d+.\d+)\s+(-*\d+.\d+)\s+(-*\d+.\d+)\s+(-*\d+.\d+)\s+(-*\d+.\d+)\s+/;
                   push(@freq,$1);
                   push(@bandwidth,$2);
                   push(@energy,$3);
                   push(@dmean,$4);
                   push(@a1,$5);
                   push(@b1,$6);
                   push(@a2,$7);
                   push(@b2,$8);
                   my($tempr1,$tempr2,$principledirection)=getPolar($5,$7,$6,$8);
                   push(@r1,$tempr1);
                   #print "*".$1."\n";
                   push(@r2,$tempr2);
                   push(@pridirection,$principledirection);
                   push(@factor,$9);
                 }
                 else {
                   $data[$i]=~ /(\d+.\d+)\s+(\d+.\d+)\s+(\d+.\d+)\s+(\d+)\s+(-*\d+.\d+)\s+/;
                   #print "*".$1."\n";
                   push(@freq,$1);
                   push(@bandwidth,$2);
                   push(@energy,$3);
                   push(@dmean,$4);
                   push(@factor,$5);
                 }
             }
        }
       if(($i>0)&&($linecount>=0)){  
	      if($timedate){
                  if(@freq<100){
                    if($sp==0){
                      if(@freq>45){
                         insert();
                      }
                    }
                    else {
                       #1d spectral wave data
                       insert1();
                    }
                  }
              }
       }
      
  print "platform is $platform_handle\n";
  print "counter is ".$counter."\n";
  if($startTime){
     print "start time is $startTime\n";
  }
  if($endTime){
     print "end time is $endTime\n";       
  }
  if($days){
     print "Days: $days\n";
  }
} #foreach provider


 $dbh->disconnect();

exit 0;

##############################################

sub escape_literals {

my $platform_url = shift;
$platform_url =~ s/&/&amp;/ ;

return $platform_url;

}


sub getMtype{
     my ($obs_type,$uom_type)=@_;
     my $sql="select m_type.row_id,uom_type.display from m_type 
              left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
              left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
              left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
              where obs_type.standard_name='$obs_type'";
               # print "\n"."$sql\n";
     my $sth = $dbh->prepare($sql);
        $sth->execute();
     my ($m_type_id,$uom_name) = $sth->fetchrow_array;
     $sth->finish();

     if(!$m_type_id){

         #updtae obs_type table
         $sql="select row_id from obs_type where standard_name='$obs_type'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($obstype_id) = $sth->fetchrow_array;
         if(!$obstype_id){
           $sql="select max(row_id) from obs_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($obstype_id) = $sth->fetchrow_array;
           $obstype_id++;
           $sql="insert into obs_type(row_id,standard_name) values($obstype_id,'$obs_type')";
           $sth = $dbh->prepare($sql);
           print "$sql\n";
           $sth->execute();
           $sth->finish();
          }
           #updtae uom_type table
           $sql="select row_id from uom_type where display='$uom_type'";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($uom_id) = $sth->fetchrow_array;
           if(!$uom_id){
             $sql="select max(row_id) from uom_type";
             $sth = $dbh->prepare($sql);
             $sth->execute();
             my ($uom_id) = $sth->fetchrow_array;
             $uom_id++;
             $sql="insert into uom_type(row_id,standard_name,display) values($uom_id,'$uom_type','$uom_type')";
             $sth = $dbh->prepare($sql);
             print "$sql\n";
             $sth->execute();
             $sth->finish();
            }

        #updtae m_scalar_type table
         $sql="select row_id from m_scalar_type where obs_type_id=$obstype_id and uom_type_id=$uom_id";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($mscalar_id) = $sth->fetchrow_array;
         if(!$mscalar_id){
           $sql="select max(row_id) from m_scalar_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($mscalar_id) = $sth->fetchrow_array;
           $mscalar_id++;
           $sql="insert into m_scalar_type(row_id,obs_type_id,uom_type_id) values($mscalar_id,$obstype_id,$uom_id)";
           print "$sql\n";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           $sth->finish();
          }

                  #update mtype table   
          $sql="select max(row_id) from m_type";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          my ($m_type_id) = $sth->fetchrow_array;
          $m_type_id++;
          $sql="insert into m_type(row_id,num_types,description,m_scalar_type_id) values($m_type_id,1,'$obs_type',$mscalar_id)";
          #print "$sql\n";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          $sth->finish();

     } #end of m_type_id

     return $m_type_id;
}

sub getSensor{
  
    my ($m_type_id,$short_name)=@_;
    my $sql="select row_id from sensor where platform_id=$platform_row_id and m_type_id=$m_type_id";
    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    # print $sql."\n";
    my ($sensor_id) = $sth->fetchrow_array;
    $sth->finish();
    if(!$sensor_id){
         #insert new record to sensor table
         $sql="select row_id from sensor_type where type_name='$short_name'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($type_id) = $sth->fetchrow_array;
         $sth->finish();
         if(!$type_id){
           $sql="select max(row_id) from sensor_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($type_id) = $sth->fetchrow_array;
           $type_id++;
           $sql="insert into sensor_type(row_id,type_name,description) values($type_id,'$short_name','$short_name')";
           $sth = $dbh->prepare($sql);
           #print "$sql\n";
           $sth->execute();
           $sth->finish();
         }
         $sql="select max(row_id) from sensor";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         ($sensor_id) = $sth->fetchrow_array;
         $sensor_id++;
         $sql="insert into sensor(row_id,row_entry_date,platform_id,type_id,m_type_id,short_name) values ($sensor_id,$timevalue,$platform_row_id,$type_id,$m_type_id,'$short_name')";
          $sth = $dbh->prepare($sql);
          #print "inserting to sensor table $sql\n";
          $sth->execute();
          $sth->finish();
    }
    return $sensor_id;
}


sub insert{
   
   #timeperiod
   #wavedirection
   #average time period
   #center frequency
   my $myfreq=join(';',@freq);
   #bandwidth
   my $myband=join(';',@bandwidth);
   #energy
   my $myenergy=join(';',@energy);
   #mean direction
   my $mydirection=join(';',@dmean);
   #a1
   my $mya1=join(';',@a1);
   #b1
   my $myb1=join(';',@b1);
   #a2
   my $mya2=join(';',@a2);
   #b2
   my $myb2=join(';',@b2);
   #r1
   my $myr1=join(';',@r1);
   #r2
   my $myr2=join(';',@r2);
   #principle wave direction
   my $myprd=join(';',@pridirection);
   #print $myprd."\n";
   #checkfactor
   my $myfactor=join(';',@factor);
   #waveheight

   my @obstype_array=("significant_wave_height","dominant_wave_period","significant_wave_to_direction","average_wave_period",
"spectral_energy","center_frequencies","fourier_coefficient_a_1","fourier_coefficient_b_1", "fourier_coefficient_a_2","fourier_coefficient_b_2","mean_wave_direction","check_factor", 'polar_coordinate_r_1','polar_coordinate_r_2','principal_wave_direction');
   my @uom_array=("m","s","deg","s","","","","","","","","","","","");
 my @obs_array=($waveheight,$timeperiod,$direction,$awp,$myenergy,$myfreq,$mya1,$myb1,$mya2,$myb2, $mydirection,$myfactor,$myr1,$myr2,$myprd);

for (my $m=0;$m<@obstype_array;$m++){
   # "\n$obstype_array[$m]\n\n";
   my $m_type_id=getMtype($obstype_array[$m],$uom_array[$m]);
   my $sensor_id=getSensor($m_type_id,$obstype_array[$m]);
        
   my $sql="select row_id from $tname where m_date='$timedate' and sensor_id=$sensor_id and m_type_id=$m_type_id";
   
   my $sth = $dbh->prepare($sql);
   $sth->execute();
   my ($row_id) = $sth->fetchrow_array;
   $sth->finish();

   if($row_id>0){;}
   else {
                        
         if($m<4){
             $sql="INSERT into $tname (row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$timedate',$m_lon,$m_lat,$missing_value,$obs_array[$m])";
              
         }
         else {
             
             if(@freq<90){
                  $sql="INSERT into $tname (row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value_list) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$timedate',$m_lon,$m_lat,$missing_value,'$obs_array[$m]')";
             }
             else {
                 $sql="";
             }
             #print @freq."\n";
         }
             if($sql ne ""){
                $sth = $dbh->prepare($sql);         
                $sth->execute();
                $sth->finish();
                $counter++;
             }
    }
 #   print "$sql\n";
}
   
}

sub insert1{
   
   #timeperiod
   #wavedirection
   #average time period
   #center frequency
   my $myfreq=join(';',@freq);
   #bandwidth
   my $myband=join(';',@bandwidth);
   #energy
   my $myenergy=join(';',@energy);
   #mean direction
   my $mydirection=join(';',@dmean);
   #checkfactor
   my $myfactor=join(';',@factor);
   #waveheight

   my @obstype_array=("significant_wave_height","dominant_wave_period","significant_wave_to_direction","average_wave_period",
"spectral_energy","center_frequencies","mean_wave_direction","check_factor");
   my @uom_array=("m","s","deg","s","","","","");
 my @obs_array=($waveheight,$timeperiod,$direction,$awp,$myenergy,$myfreq, $mydirection,$myfactor);

for (my $m=0;$m<@obstype_array;$m++){
   # "\n$obstype_array[$m]\n\n";
   my $m_type_id=getMtype($obstype_array[$m],$uom_array[$m]);
   my $sensor_id=getSensor($m_type_id,$obstype_array[$m]);
        
   my $sql="select row_id from $tname where m_date='$timedate' and sensor_id=$sensor_id and m_type_id=$m_type_id";
   
   my $sth = $dbh->prepare($sql);
   $sth->execute();
   my ($row_id) = $sth->fetchrow_array;
   $sth->finish();

   if($row_id>0){;}
   else {
                        
         if($m<4){
             $sql="INSERT into $tname (row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$timedate',$m_lon,$m_lat,$missing_value,$obs_array[$m])";
              
         }
         else {
             
             if(@freq<90){
                  if($obs_array[$m]){ 
                  $sql="INSERT into $tname (row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value_list) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$timedate',$m_lon,$m_lat,$missing_value,'$obs_array[$m]')";
                  }
                  else{$sql="";}
             }
             else {
                 $sql="";
             }
             #print @freq."\n";
         }
             if($sql ne ""){
                $sth = $dbh->prepare($sql);         
                $sth->execute();
                $sth->finish();
                $counter++;
             }
    }

#    print "$sql\n";
}
   
}


sub getPolar{
#  Converts CDIP format wave spectra A & B coefficients to
#  polar R and Alpha coefficients.
# r1 and r2 are the first and second normalized polar
# coordinates of the Fourier coefficients and are nondimensional.
# ALPHA1 and ALPHA2 are respectively mean and principal wave
# directions.
# In terms of Longuet-Higgins Fourier Coefficients
#  r1 = sqrt(a1*a1+b1*b1)
#  r2 = sqrt(a2*a2+b2*b2)
#  alpha11 = atan2(b1,a1)
#  alpha2 = 0.5*atan2(b2,a2) + {0. or 180.}
#  Note - find alpha2 that's closest to alpha1, add 180 if necessary 

my $r2d=180/(4*atan2(1,1));

    my ($a1,$a2,$b1,$b2)=@_;
    my $r1 = sqrt($a1*$a1+$b1*$b1);
    my $r2 = sqrt($a2*$a2+$b2*$b2);
    my $alpha1 = $r2d*atan2($b1,$a1);
    my $alpha2 = $r2d*0.5*atan2($b2,$a2);
       if(abs($alpha2-$alpha1) > 90) { 
			$alpha2 += 180;
			if ($alpha2 > 360) { $alpha2 -= 360; }
       }
       if ($alpha2 < 0) { $alpha2 += 360; }
       if ($alpha1 < 0) { $alpha1 += 360; }
    $r1=sprintf("%.4f",$r1);
    $r2=sprintf("%.4f",$r2);
    $alpha2=sprintf("%.2f",$alpha2);

    #print "r1=$r1 r2=$r2 alph2=$alpha2\n";
    return ($r1,$r2,$alpha2);
}
